package com.bst.base.utils;

import com.alibaba.druid.DbType;
import com.alibaba.druid.pool.DruidDataSource;
import com.bst.common.exception.base.BaseException;
import com.bst.common.exception.base.BaseRuntimeException;
import com.bst.common.utils.sql.SqlUtil;
import com.bst.common.vo.MetaDataVO;
import com.bst.base.domain.BaseDb;
import org.apache.commons.lang3.tuple.ImmutableTriple;
import org.apache.commons.lang3.tuple.Triple;
import org.springframework.boot.jdbc.DataSourceBuilder;
import com.bst.framework.datasource.DynamicDataSource;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class DBUtil {


    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, Triple<String, Integer, String>> getColumnMetaDataByTableName(
            Connection conn, String tableName) {
        Triple<List<String>, List<Integer>, List<String>> columnMetaData = DBUtil.getMetaDataBySql(conn, "select * from "+tableName);
        Map<String, Triple<String, Integer, String>> map = new HashMap<>();
        List<String> left = columnMetaData.getLeft();
        List<Integer> middle = columnMetaData.getMiddle();
        List<String> right = columnMetaData.getRight();
        for (int i = 0; i < left.size(); i++) {
            Triple<String, Integer, String> t = new ImmutableTriple(left.get(i), middle.get(i), right.get(i));
            map.put(left.get(i), t);
        }
        return map;
    }

    public static Map<String, Triple<String, Integer, String>> getColumnMetaDataBySql(
            Connection conn, String sql) {
        Triple<List<String>, List<Integer>, List<String>> columnMetaData = DBUtil.getMetaDataBySql(conn, sql);
        Map<String, Triple<String, Integer, String>> map = new HashMap<>();
        List<String> left = columnMetaData.getLeft();
        List<Integer> middle = columnMetaData.getMiddle();
        List<String> right = columnMetaData.getRight();
        for (int i = 0; i < left.size(); i++) {
            Triple<String, Integer, String> t = new ImmutableTriple(left.get(i), middle.get(i), right.get(i));
            map.put(left.get(i), t);
        }
        return map;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Triple<List<String>, List<Integer>, List<String>> getMetaDataBySql(
            Connection conn, String queryColumnSql) {
        Statement statement = null;
        ResultSet rs = null;
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(queryColumnSql);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaData(rsMetaData);
        } catch (SQLException e) {
            throw new BaseRuntimeException(String.format("获取表的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", queryColumnSql), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, null);
        }
    }

    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(ResultSetMetaData rsMetaData) {

        Triple<List<String>, List<Integer>, List<String>> columnMetaData = new ImmutableTriple<>(
                new ArrayList<>(), new ArrayList<>(),
                new ArrayList<>());
        try {
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                columnMetaData.getLeft().add(rsMetaData.getColumnLabel(i + 1));
                columnMetaData.getMiddle().add(rsMetaData.getColumnType(i + 1));
                columnMetaData.getRight().add(rsMetaData.getColumnTypeName(i + 1));
            }
            return columnMetaData;

        } catch (SQLException e) {
            throw new BaseRuntimeException(String.format("获取表字段的元信息时失败. 请联系 DBA 核查该库、表信息."), e);
        }
    }

    public static DruidDataSource createDataSource(BaseDb baseDb) throws InterruptedException {
        DruidDataSource datasource = DataSourceBuilder.create()
                .type(DruidDataSource.class)
                .driverClassName(baseDb.getDriver())
                .url(baseDb.getUrl())
                .username(baseDb.getNaUse())
                .password(baseDb.getPwd())
                .build();
        return datasource;
    }

    public static void closeDataSource(Object dataSource) {
        if (dataSource == null) return;
        if (dataSource.getClass().equals(DruidDataSource.class)) {
            ((DruidDataSource) dataSource).close();
        }
    }

    public static ResultSet query(Connection conn, String sql, int fetchSize, int queryTimeout)
            throws SQLException {
        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(fetchSize);
        stmt.setQueryTimeout(queryTimeout);
        return stmt.executeQuery(sql);
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, MetaDataVO> getTableColumnMetaDataToMetaDataVO(DruidDataSource ds, String tableName) throws BaseException {
        String queryColumnSql = "select * from " + tableName;
        return getColumnMetaDataToMetaDataVO(ds, queryColumnSql);

    }

    /**
     * Map<ColumnName, MetaDataVO>
     *
     * @param dataSource
     * @param sql
     * @return
     * @throws BaseException
     */
    public static Map<String, MetaDataVO> getColumnMetaDataToMetaDataVO(
            DruidDataSource dataSource, String sql) throws BaseException {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        String qSQL = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            qSQL = SqlUtil.buildSql(sql, new java.util.Date(), new Date(), dataSource.getDbType());
            rs = statement.executeQuery(qSQL);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaDataToMetaDataVO(connection,rsMetaData);
        } catch (SQLException e) {
            //e.printStackTrace();
            throw new BaseException(String.format("获取查询:%s 的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", qSQL));
        } finally {
            DBUtil.closeDBResources(rs, statement, connection);
        }
    }

    /**
     * Map<ColumnName, MetaDataVO>
     *
     * @param rsMetaData
     * @return
     * @throws BaseException
     */
    public static Map<String, MetaDataVO> getColumnMetaDataToMetaDataVO(Connection connection,ResultSetMetaData rsMetaData) throws BaseException {

        Map<String, MetaDataVO> columnMetaData = new LinkedHashMap<>();
        try {
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                MetaDataVO metaDataVO = new MetaDataVO();
                metaDataVO.setColumnName(rsMetaData.getColumnLabel(i + 1).toUpperCase());
                metaDataVO.setColumnType(rsMetaData.getColumnType(i + 1));
                metaDataVO.setColumnTypeName(rsMetaData.getColumnTypeName(i + 1));
                metaDataVO.setColumnPrecision(rsMetaData.getPrecision(i + 1));
                metaDataVO.setColumnScale(rsMetaData.getScale(i + 1));
                metaDataVO.setColumnComment(getColumnComment(connection,rsMetaData.getSchemaName(i+1),rsMetaData.getTableName(i+1),rsMetaData.getColumnName(i+1)));
                columnMetaData.put(metaDataVO.getColumnName(), metaDataVO);

            }
            return columnMetaData;

        } catch (SQLException e) {
            throw new BaseException(String.format("获取表字段的元信息时失败. 请联系 DBA 核查该库、表信息."));
        }
    }

    private static String getColumnComment(Connection connection, String schema, String table, String column) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet columns = metaData.getColumns(null, schema, table, column);
        if (columns.next()) {
            return columns.getString("REMARKS");
        }
        return "";
    }

    public static void closeDBResources(ResultSet rs, Statement stmt,
                                        Connection conn) {
        if (null != rs) {
            try {
                rs.close();
            } catch (SQLException unused) {
            }
        }

        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException unused) {
            }
        }

        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException unused) {
            }
        }
    }

    public static String queryCreatTableSql(String tableName,Integer idDb) throws SQLException {
        DruidDataSource dataSource = DynamicDataSource.getDataSourceById(idDb+"");
        Connection mysqlConn = null;
        ResultSet rs=null;Statement stmt=null;
        try {
            mysqlConn = dataSource.getConnection();
            stmt = mysqlConn.createStatement();
            if(DbType.mysql.equals(dataSource.getDbType())) {
                rs = stmt.executeQuery("SHOW CREATE TABLE `" + tableName + "`");
                return rs.getString(2);
            } else if(DbType.sqlserver.equals(dataSource.getDbType())) {
                rs = stmt.executeQuery("sp_help '" + tableName + "'");
                return rs.getString(2);
            } else if(DbType.oracle.equals(dataSource.getDbType())) {
                rs = stmt.executeQuery("SELECT DBMS_METADATA.GET_DDL('TABLE', '" + tableName + "') FROM DUAL");
                return rs.getString(1);
            } else if(DbType.postgresql.equals(dataSource.getDbType())) {
                rs = stmt.executeQuery("SELECT pg_catalog.pg_get_ddl('base_type'::regclass, '\"" + tableName + "\"') AS ddl FROM pg_catalog.pg_tables WHERE schemaname='public' AND tablename='" + tableName + "' LIMIT 1");
                return rs.getString("ddl");
            } else {
                rs = stmt.executeQuery("SHOW CREATE TABLE `" + tableName + "`");
                return rs.getString(2);
            }
        } finally {
            DBUtil.closeDBResources(rs, stmt, mysqlConn);
        }
    }
}

